package com.gusto.engine.colfil.neighborhood.storedistance.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.gusto.engine.colfil.Distance;
import com.gusto.engine.colfil.neighborhood.storedistance.DistancePersistenceDAO;

public class DistancePersistenceDAOImpl implements DistancePersistenceDAO {
	
	private Logger log = Logger.getLogger(getClass());
	
	private String CONFIG_USER;
	private String CONFIG_ITEM;
	
	public void setCONFIG_USER(String config_user) {
		CONFIG_USER = config_user;
	}
	public void setCONFIG_ITEM(String config_item) {
		CONFIG_ITEM = config_item;
	}
	
	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	private class ConfigItem {
		public String table;
		public String id1;
		public String id2;
		public String distance;
		public String common;
	}
	
	private ConfigItem getConfigItem(String tablecolumn) {
		try {
			ConfigItem configItem = new ConfigItem();
			configItem.table = tablecolumn.substring(0, tablecolumn.indexOf("->")).trim();
			String rest = tablecolumn.substring(tablecolumn.indexOf("->") + 2).trim();
			
			configItem.id1 = rest.substring(0, rest.indexOf(",")).trim();
			rest = rest.substring(rest.indexOf(",") + 1).trim();
			
			configItem.id2 = rest.substring(0, rest.indexOf(",")).trim();
			rest = rest.substring(rest.indexOf(",") + 1).trim();
			
			configItem.distance = rest.substring(0, rest.indexOf(",")).trim();
			rest = rest.substring(rest.indexOf(",") + 1).trim();
			
			configItem.common = rest;
			return configItem;
		}
		catch (Exception ex) {
			return null;
		}
	}
	
	private String decodeFormula(ConfigItem ci, String formula) {
		String result = formula;
		
		result = result.replaceAll("%DISTANCE%", ci.distance);
		result = result.replaceAll("%COMMON%", ci.common);
		
		return result;
	}
	
	private void updateDistanceXXX(ConfigItem ci, long id1, long id2, Distance distance) {
		if (id1 > id2) {
			long tmp = id1;
			id1 = id2;
			id2 = tmp;
		}
		 
		String sql_i = "insert /*+ append */ into " + ci.table + " " + 
		"(" + ci.common + ", " + ci.distance + ", " + ci.id1 + ", " + ci.id2 + ") values (?, ?, ?, ?)";
		
		String sql_u = "update /*+ append */ " + ci.table + " " +
		"set " + ci.common + " = ?, " + ci.distance + " = ? where " + ci.id1 + " = ? and " + ci.id2 + " = ? ";
		
		Object[] params = new Object[] { distance.getCount(), distance.getDistance(), id1, id2 };
		int[] types = new int[] { Types.INTEGER, Types.DOUBLE, Types.INTEGER, Types.INTEGER };
		
		try {
			if (existsXXX(ci, id1, id2)) {
				log.debug("Item Link exist");
				log.debug("SQL update " + sql_u);
				jdbcTemplate.update(sql_u, params, types);
			}
			else {
				log.debug("Item Link doesn't exist");
				log.debug("SQL insert " + sql_i);
				jdbcTemplate.update(sql_i, params, types);
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void updateItemDistance(long item1, long item2, Distance distance) {
		ConfigItem ci = getConfigItem(this.CONFIG_ITEM);
		log.info("Update item distance " + item1 + " " + item2 + " => " + distance.getDistance());
		updateDistanceXXX(ci, item1, item2, distance);
	}
	
	public void updateUserDistance(long user1, long user2, Distance distance) {
		ConfigItem ci = getConfigItem(this.CONFIG_USER);
		log.info("Update user distance " + user1 + " " + user2 + " => " + distance.getDistance());
		updateDistanceXXX(ci, user1, user2, distance);
	}
	
	private boolean existsXXX(ConfigItem ci, long id1, long id2) {
		log.debug("Check exists " + id1 + " " + id2);
		String sql = "select count(*) from " + ci.table + " where " + ci.id1 + "=? and " + ci.id2 + "=? ";
		Object[] params = new Object[] { id1, id2 };
		int[] types = new int[] { Types.INTEGER, Types.INTEGER };
		int count = jdbcTemplate.queryForInt(sql, params, types);
		
		log.info("Check exists " + id1 + " " + id2 + " => " + (count > 0));
		return (count > 0);
	}
	
	@SuppressWarnings("unchecked")
	private List<Distance> getNearXXX(ConfigItem ci, long id) {
		log.debug("Near " + id);
		String sql = "select * from " + ci.table + " " +
				     "where (" + ci.id1 + "=? or " + ci.id2 + "=?) " +
				     "order by " + ci.distance + " desc ";
		log.debug("SQL " + sql);
		
		Object[] params = new Object[] { id, id };
		int[] types = new int[] { Types.INTEGER, Types.INTEGER };
		List<Distance> result = jdbcTemplate.query(sql, params, types, new DistanceRowMapper(ci));
		return result;
	}
	
	@SuppressWarnings("unchecked")
	private List<Distance> getNearXXX(ConfigItem ci, String formula, long id, int MIN_SHARED_ITEM, double MIN_SIMILARITY) {
		log.debug("Near " + id);
		String sql = "select " + ci.id1 + ", " + ci.id2 + ", " + ci.common + ", ( " + formula + " ) as dist " +
					 "from " + ci.table + " " +
				     "where (" + ci.id1 + "=? or " + ci.id2 + "=?) " +
				     " and " + ci.common + " >= ? \n" +
					 " and " + formula + " >= ? \n" +
				     "order by " + formula + " desc ";
		
		log.debug("SQL " + sql);
		
		Object[] params = new Object[] { id, id, MIN_SHARED_ITEM, MIN_SIMILARITY };
		int[] types = new int[] { Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.DOUBLE };
		List<Distance> result = jdbcTemplate.query(sql, params, types, new DistanceRowMapper(ci));
		return result;
	}
	
	public List<Distance> getNearUsers(long userId) {
		ConfigItem ci = getConfigItem(this.CONFIG_USER);
		log.info("Near Users to " + userId);
		return getNearXXX(ci, userId);
	}
	
	public List<Distance> getNearItems(long itemId) {
		ConfigItem ci = getConfigItem(this.CONFIG_ITEM);
		log.info("Near Items to " + itemId);
		return getNearXXX(ci, itemId);
	}

	public List<Distance> getNearUsers(long userId, String initialFormula, int MIN_SHARED_ITEM, double MIN_SIMILARITY) {
		ConfigItem ci = getConfigItem(this.CONFIG_USER);
		log.info("Near Users (constraints) to " + userId);
		return getNearXXX(ci, decodeFormula(ci, initialFormula), userId, MIN_SHARED_ITEM, MIN_SIMILARITY);
	}
	
	public List<Distance> getNearItems(long itemId, String initialFormula, int MIN_SHARED_ITEM, double MIN_SIMILARITY) {
		ConfigItem ci = getConfigItem(this.CONFIG_ITEM);
		log.info("Near Items (constraints) to " + itemId);
		return getNearXXX(ci, decodeFormula(ci, initialFormula), itemId, MIN_SHARED_ITEM, MIN_SIMILARITY);
	}
	
	public void clean() {
		log.info("Cleaning distances");
		ConfigItem ciu = getConfigItem(this.CONFIG_USER);
		ConfigItem cii = getConfigItem(this.CONFIG_ITEM);
		jdbcTemplate.update("delete from " + ciu.table);
		jdbcTemplate.update("delete from " + cii.table);
	}
	
	class DistanceRowMapper implements RowMapper {
		ConfigItem ci;
		
		public DistanceRowMapper(ConfigItem ci) {
			this.ci = ci;
		}
		
		public Object mapRow(ResultSet rs, int index) throws SQLException {
			Distance dist = new Distance();
			dist.setId1(rs.getLong(ci.id1));
			dist.setId2(rs.getLong(ci.id2));
			dist.setCount(rs.getInt(ci.common));
			//dist.setDistance((Double)rs.getObject(ci.distance));
			dist.setDistance((Double)rs.getObject(ci.distance));
			
		    return dist;
		}
	}
	
}

